using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using System.Security;
using System.Security.Permissions;
using System.Threading;

//using System.Web;

#if Oracle
using Oracle.DataAccess.Client;
#endif
[assembly: log4net.Config.XmlConfigurator]

namespace DataBroker.Dal
{
    /// <summary>
    /// Class that handles connection to the database.
    /// </summary>
    internal abstract class D2BkConnection : MarshalByRefObject, IDisposable
    {
        private static readonly log4net.ILog Log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        [ThreadStatic]
        private static DateTime LastConnectionTime = DateTime.MinValue;

        [ThreadStatic] 
        internal static List<D2BkUOW> UnitOfWork = null;

        private D2BkConnection()
        {
        }

        private static string GetConnectionString()
        {
            D2BkSettingsCollection settings = new D2BkSettingsCollection();
            string sConnectionString = settings["connectionString"];
            return sConnectionString;
        }

        static internal string GetCurrentDBIndex()
        {
            return string.Format("{0}{1}", D2BkSettingsCollection.GetCurrentDatabase(), Thread.CurrentThread.ManagedThreadId);
        }
        static public string ProviderType()
        {
            D2BkSettingsCollection settings = new D2BkSettingsCollection();
            string sConnString = settings["providerType"];
            if (sConnString == null)
                return "System.Data.SqlClient.SqlConnection";
            return sConnString.Length == 0 ? "System.Data.SqlClient.SqlConnection" : sConnString;
        }

        static public string DBOwner()
        {
            D2BkSettingsCollection settings = new D2BkSettingsCollection();
            string dbOwner = settings["dbOwner"];
            if (dbOwner == null)
                return "";
            if (dbOwner.Length == 0)
                return "";
            return string.Format("{0}.", dbOwner);
        }

        static public CommandBehavior DBDataReaderCommandBehavior()
        {
            D2BkSettingsCollection settings = new D2BkSettingsCollection();
            string dbDataReaderCommandBehavior = settings["dbDataReaderCommandBehavior"];
            if (dbDataReaderCommandBehavior == null)
                return CommandBehavior.KeyInfo;
            if (dbDataReaderCommandBehavior.Length == 0)
                return CommandBehavior.KeyInfo;
            try
            {
                return (CommandBehavior) Enum.Parse(typeof (CommandBehavior), dbDataReaderCommandBehavior);
            }
            catch
            {
                return CommandBehavior.KeyInfo;
            }
        }

        static private int RetryTimes
        {
            get
            {
                D2BkSettingsCollection settings = new D2BkSettingsCollection();
                string dbSetting = settings["RetryTimes"];
                int retryTimes = 0;
                if (dbSetting == null)
                    return retryTimes;
                if (dbSetting.Length == 0)
                    return retryTimes;
                int.TryParse(dbSetting, out retryTimes);
                return retryTimes;
            }
        }

        static private string GetDbSetting(string setting, string defaultSetting)
        {
            D2BkSettingsCollection settings = new D2BkSettingsCollection();
            string dbSetting = settings[setting];
            if (dbSetting == null)
                return defaultSetting;
            if (dbSetting.Length == 0)
                return defaultSetting;
            return dbSetting;
        }

        static private string GetDbSetting(string setting)
        {
            return GetDbSetting(setting, "");
        }


        static public string DBVersion()
        {
            return GetDbSetting("dbVersion");
        }

        static public string DBBrackets()
        {
            return GetDbSetting("dbBrackets");
        }

        static public string DBTableAS()
        {
            return GetDbSetting("dbTableAS");
        }

        static public string GetAssemblyName()
        {
            D2BkSettingsCollection settings = new D2BkSettingsCollection();
            string sAssemblyName = settings["assemblyName"];
            if (sAssemblyName == null)
            {
                Log.WarnFormat("assemblyName not found; return System.Data");
                return "System.Data";
            }
            if (sAssemblyName.Length == 0)
            {
                Log.WarnFormat("assemblyName length is zero; return System.Data");
                return "System.Data";
            }
            return sAssemblyName;
        }

        static public string ParameterName(string name)
        {
            if (ProviderType().IndexOf("SqlClient") >= 0 || ProviderType().IndexOf("SQLite") >= 0)
                return " " + name + " ";
            if (ProviderType().IndexOf("Oracle") >= 0)
                return " " + name.Replace("@", ":") + " ";
            //return ":"+D2Bk.GetParm().CurrentField.ToString();
            return " ? ";
        }


        static internal string ThreadId()
        {
            return Thread.CurrentThread.GetHashCode().ToString(CultureInfo.InvariantCulture) + D2BkSettingsCollection.GetCurrentDatabase();
        }


        [ThreadStatic]
        private static IDbCommand _CommandObject;
        public static IDbCommand CommandObject
        {
            get
            {
                return _CommandObject;
            }
            set
            {
                _CommandObject = value;
            }
        }


        static internal DateTime GetLastDBConnectTime()
        {
            if (LastConnectionTime == DateTime.MinValue)
                LastConnectionTime = DateTime.Now;
            return LastConnectionTime;
        }

        static private void SetLastDBConnectTime()
        {
            if (LastConnectionTime == DateTime.MinValue)
            {
                LastConnectionTime = DateTime.Now;
            }
        }


        /// <summary>
        /// Remove connections when changing from one database to another
        /// </summary>
        /// <param name="strDB"></param>
        internal static void RemoveConnections(string strDB)
        {
            if (CommandObject != null)
                CommandObject.Dispose();
        }

        internal static void RemoveCurrentConnection()
        {
            if (CommandObject == null) return;
            CommandObject.Dispose();
            CommandObject = null;
        }

        private static void Connect()
        {
            if (CommandObject != null)
            {
                if (CommandObject.Connection != null)
                {
                    return;
                }
            }
            IDbConnection conn;
            if (ProviderType().Length > 0)
            {
                // Search through all referenced assemblies
                Assembly referencedAssembly = Assembly.Load(GetAssemblyName()) ?? Assembly.Load(GetAssemblyName());
                D2Bk.LogDebug(string.Format("Referenced assembly: {0}", referencedAssembly));
                string providerType = ProviderType();
                Type connType = referencedAssembly.GetType(providerType);
                D2Bk.LogDebug(string.Format("Connection Type: {0}", connType));
                try
                {
                    new PermissionSet(PermissionState.Unrestricted).Assert();
                    conn = (IDbConnection)Activator.CreateInstance(connType);
                    conn.ConnectionString = GetConnectionString();
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message, ex);
                    throw;
                }
                finally
                {
                    CodeAccessPermission.RevertAssert();
                }
            }
            else
            {
                conn = new SqlConnection(GetConnectionString());
            }

            if (CommandObject != null)
            {
                if (!string.IsNullOrEmpty(CommandObject.CommandText))
                {
                    CommandObject.Connection = conn;
                    return;
                }
            }
            CommandObject = conn.CreateCommand();
            CommandObject.CommandTimeout = 4500;
        }

        /// <summary>
        /// Begins a new transaction. 
        /// </summary>
        static internal void BeginTransaction()
        {
            UnitOfWork = new List<D2BkUOW>();
            OpenForUpdate();
        }

        /// <summary>
        /// Begins a new transaction. 
        /// </summary>
        static internal void BeginTransaction(IsolationLevel isolation)
        {
            OpenForUpdate(isolation);
        }

        /// <summary>
        /// Commits a transaction.
        /// </summary>
        static internal void Commit()
        {
            if (CommandObject == null)
                return;
            UnitOfWork.Clear();
            UnitOfWork = null;
            IDbTransaction iDbTrans = CommandObject.Transaction;
            if (iDbTrans != null)
            {
                try
                {
                    new PermissionSet(PermissionState.Unrestricted).Assert();
                    iDbTrans.Commit();
                    iDbTrans.Dispose();
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message, ex);
                    throw;
                }
                finally
                {
                    CodeAccessPermission.RevertAssert();
                }
                CommandObject.Transaction = null;
            }
            Close();
        }

        static internal void Rollback()
        {
            Rollback(true);
        }
        
        
        /// <summary>
        /// Rolls back a transaction.
        /// </summary>
        static internal void Rollback(bool clear)
        {
            if (clear)
            {
                UnitOfWork.Clear();
                UnitOfWork = null;
            }
            if (CommandObject == null)
                return;
            IDbTransaction iDbTrans = CommandObject.Transaction;
            if (iDbTrans != null)
            {
                try
                {
                    new PermissionSet(PermissionState.Unrestricted).Assert();
                    iDbTrans.Rollback();
                    iDbTrans.Dispose();
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message, ex);
                    throw;
                }
                finally
                {
                    CodeAccessPermission.RevertAssert();
                }
                CommandObject.Transaction = null;
            }
            Close();
        }

        static private void OpenForUpdate()
        {
            OpenForUpdate(IsolationLevel.ReadCommitted);
        }
        /// <summary>
        /// Opens a new connection and starts a transaction.
        /// </summary>
        static private void OpenForUpdate(IsolationLevel isolation)
        {
            Open();
            if (CommandObject.Transaction == null)
            {
                try
                {
                    new PermissionSet(PermissionState.Unrestricted).Assert();
                    IDbConnection conn = CommandObject.Connection;
                    CommandObject.Transaction = conn.BeginTransaction(isolation);
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message, ex);
                    throw;
                }
                finally
                {
                    CodeAccessPermission.RevertAssert();
                }
            }
            else
            {
                Log.Warn("OpenForUpdate: Trying to open a new transaction when one already exists!");
            }
        }

        /// <summary>
        /// Opens a connection to the database.
        /// </summary>
        static internal void Open()
        {
            bool retry;
            for (int i = 0; i < 3; i++)
            {
                retry = false;
                try
                {

                    new PermissionSet(PermissionState.Unrestricted).Assert();
                    Connect();
                    if (CommandObject == null)
                        return;
                    if (CommandObject.Transaction != null)
                    {
                        //don't close if in the middle of a transaction.
                        return;
                    }
                    DateTime last = GetLastDBConnectTime();
                    TimeSpan delta = DateTime.Now.Subtract(last);
                    SetLastDBConnectTime();
                    if (delta.Seconds > 60)
                    {
                        CommandObject.CommandText = "SELECT WHATEVER FROM ANYWHERE";
                        if (CommandObject.Connection.State == ConnectionState.Closed)
                        {
                            CommandObject.Connection.Open();
                        }
                        CommandObject.ExecuteReader();
                        CommandObject.CommandText = "";
                        Close();
                        Connect();
                    }
                }
                catch (ArgumentException)
                {
                    throw;
                }
                catch (Exception e)
                {
                        Log.Warn(e.Message);
                    Close();
                    if (CommandObject != null)
                    {
                        CommandObject.Connection.Dispose();
                        CommandObject.Connection = null;
                    }
                    retry = true;
                }
                finally
                {
                    CodeAccessPermission.RevertAssert();
                }
                if (!retry)
                    break;
            }
            if (CommandObject == null)
                return;
            IDbConnection conn = CommandObject.Connection;
            if (conn.State == ConnectionState.Closed)
            {
                for (int i = 0; i < RetryTimes || RetryTimes == 0; i++)
                {
                    retry = false;
                    try
                    {
                        conn.Open();
                    }
                    catch (SqlException sqlex)
                    {
                        HandleSqlException(sqlex);
                        retry = true;
                    }
                    catch (InvalidOperationException ex)
                    {
                        conn.Close();
                        retry = true;
                        Log.Info(ex.Message);
                    }
                    catch (OdbcException ex)
                    {
                        if (ex.Message.ToLower().Contains("password"))
                        {
                            if (D2Bk.ButtonEvent != null)
                                D2Bk.ButtonEvent(conn, D2BkSettingsCollection.GetCurrentDatabase());
                            throw new InvalidUserException();
                        }
                    }
                    finally
                    {
                        if (retry)
                        {
                            if (CommandObject != null)
                                if (CommandObject.Connection != null)
                                {
                                    CommandObject.Connection.Dispose();
                                    CommandObject.Connection = null;
                                }
                            Connect();
                            if (CommandObject != null)
                                conn = CommandObject.Connection;
                        }
                    }
                    if (!retry)
                        break;
                    Thread.Sleep(1000 * i);
                }
            }
            else
            {
                Log.Warn("D2BkConnection::Open - Connection already open");
            }
        }
        /// <summary>
        /// Close transaction, command and connection to the database.
        /// </summary>
        static internal void Close()
        {
            if (CommandObject == null)
                return;
            try
            {
                if (CommandObject.Transaction != null)
                {
                    //don't close if in the middle of a transaction.
                    return;
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message, ex);
            }
            CommandObject.CommandText = null;
            CommandObject.CommandTimeout = 0;
            if (CommandObject.Connection != null)
            {
                CommandObject.Connection.Close();
                CommandObject.Connection = null;
            }
            if (ProviderType().IndexOf("Oracle") < 0 && ProviderType().IndexOf("SQLite") < 0)
                CommandObject.Dispose(); //if call Dispose, Oracle doesn't work - need to find out why.
        }

        static internal void HandleSqlException(SqlException sqlex)
        {
            string errMsg = string.Format("{0} Server:{5} Error:{3}; Severity={1}; Procedure:{4} Line: {2}; State:{6}", sqlex.Message, sqlex.Class, sqlex.LineNumber, sqlex.Number, sqlex.Procedure, sqlex.Server, sqlex.State);
            if (sqlex.Class < 10)  
            {
                D2Bk.LogWarn("{0} - will retry.", errMsg);
            }
            else
                if (sqlex.Class > 16 && sqlex.Class <= 20 /*&& sqlex.Number != 53*/)
                {
                    D2Bk.LogWarn("{0} - will retry.", errMsg);
                    if (sqlex.Class == 20)
                        SqlConnection.ClearAllPools();
                }
                else
                {
                    D2Bk.LogWarn(errMsg);
                    if (sqlex.Class == 11 && sqlex.Number == -2) // timeout
                        return;
                    Log.Error(sqlex.Message);
                    if (sqlex.Message.Contains("Login failed"))
                        throw new D2BkException("Bad connection string; please check your configuration file");
                    throw sqlex;
                }
        }

        static internal IDataReader ExecuteReader(CommandBehavior beh)
        {
            for (int i = 0; i < RetryTimes || RetryTimes == 0; i++)
            {
                try
                {
                    return ExecuteReader(beh, CommandType.Text);
                }
                catch (SqlException sqlex)
                {
                    HandleSqlException(sqlex);
                }
                catch (InvalidOperationException ioex)
                {
                    D2Bk.LogWarn("Exception: {0}; will recover if possible.", ioex.Message);
                }
                catch (Exception ex)
                {
                    D2Bk.LogFatal("Exception: {0}; should add recovery code if possible.", ex.Message);
                    throw;
                }
                RecoverConnection(i);
            }
            D2Bk.LogFatal("Could not recover");
            return default(IDataReader);
        }

        private static void RecoverConnection(int tries)
        {
            if (CommandObject == null)
                return;
            Thread.Sleep(tries * 1000);
            if (CommandObject.Connection != null)
            {
                CommandObject.Connection.Dispose();
                CommandObject.Connection = null;
            }
            Open();
        }

        /// <summary>
        /// Executes a query.
        /// </summary>
        static internal IDataReader ExecuteReader(CommandBehavior beh, CommandType commandType)
        {
            IDataReader idr = default(IDataReader);
            if (CommandObject == null)
                return idr;
            CommandObject.CommandType = commandType;
            try
            {
                new PermissionSet(PermissionState.Unrestricted).Assert();
                idr = CommandObject.ExecuteReader(beh);
            }
            catch (SecurityException ex)
            {
                Log.Error(ex.Message, ex);
                throw;
            }
            finally
            {
                CodeAccessPermission.RevertAssert();
            }
            return idr;
        }

        internal static DataSet ExecuteDataset(string spName)
        {
            return ExecuteDataset(spName, null);
        }

        internal static DataSet ExecuteDataset(string spName, params object[] parameterValues)
        {
            for (int i = 0; i < RetryTimes || RetryTimes == 0; i++)
            {
                try
                {
                    if (spName.ToLower(CultureInfo.InvariantCulture).IndexOf("select ") >= 0)
                    {
                        return ExecuteDatasetForSelectStatement(spName, parameterValues);
                    }
                    return ExecuteDatasetForStoredProcedure(spName, parameterValues);
                }
                catch (SqlException sqlex)
                {
                    HandleSqlException(sqlex);
                }
                catch (InvalidOperationException ioex)
                {
                    Thread.Sleep(i * 1000);
                    D2Bk.LogWarn("Exception: {0}; will recover if possible.", ioex.Message);
                }
                catch (Exception ex)
                {
                    D2Bk.LogFatal("Exception: {0}; should add recovery code if possible.", ex.Message);
                    throw;
                }
                RecoverConnection(i);
            }
            D2Bk.LogFatal("Could not recover");
            return default(DataSet);
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="sql">Stored procedure name.</param>
        /// <param name="parameterValues">Any number of objects (variable parameter) to be assigned as the input values of the stored procedure</param>
        /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
        internal static int ExecuteSP(string sql, params object[] parameterValues)
        {
            //if we receive parameter values, we need to figure out where they go
            if (CommandObject == null)
                return -1;
            CommandObject.CommandType = CommandType.StoredProcedure;
            CommandObject.CommandText = sql;
            D2Bk.LogDebug("EXEC: {0}", sql);
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    GetSpParameterSet(sql, false);

                //assign the provided values to these parameters based on parameter order
                for (int i = 0; i < CommandObject.Parameters.Count; i++)
                {
                    IDbDataParameter par = (IDbDataParameter)CommandObject.Parameters[i];
                    par.Value = parameterValues[i];
                    D2Bk.LogDebug("{0}: {1}", par.ParameterName, par.Value);
                }
            }
            return ExecuteNonQuery();
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="sql">Stored procedure name.</param>
        /// <param name="parameterValues">Any number of objects (variable parameter) to be assigned as the input values of the stored procedure</param>
        /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
        internal static DataSet ExecuteDataSetFromSP(string sql, params object[] parameterValues)
        {

            //if we receive parameter values, we need to figure out where they go
            if (CommandObject == null)
                return default(DataSet);
            CommandObject.CommandType = CommandType.StoredProcedure;
            CommandObject.CommandText = sql;
            D2Bk.LogDebug("EXEC: {0}", sql);
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                GetSpParameterSet(sql, false);

                //assign the provided values to these parameters based on parameter order
                for (int i = 0; i < CommandObject.Parameters.Count; i++)
                {
                    IDbDataParameter par = (IDbDataParameter)CommandObject.Parameters[i];
                    par.Value = parameterValues[i];
                    D2Bk.LogDebug("{0}: {1}", par.ParameterName, par.Value);
                }
            }

#if Oracle

            char[] delim = new char[] { ',', ' ', ')' };
            cmd.CommandText = sql.Replace("@", ":");

            if (cmd.CommandText.IndexOf("(") >= 0)
            {
                cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.IndexOf("("));
            }

            string[] paramList = sql.Split(':');
            string[] outParm = sql.Split('@');
            if (sql.IndexOf(":") >= 0)
            {
                for (int i = 1; i < paramList.Length; i++)
                {
                    string parName = paramList[i];
                    if (parName.IndexOfAny(delim) >= 0)
                        parName = parName.Remove(parName.IndexOfAny(delim));
                    if (parName.IndexOfAny(delim) >= 0)
                        parName = parName.Remove(parName.IndexOfAny(delim));
                    AddOraParameter(parName, parameterValues[i - 1]);
                }
            }
            if (sql.IndexOf("@") >= 0)
            {
                for (int i = 1; i < outParm.Length; i++)
                {
                    string parName = outParm[i];
                    if (parName.IndexOfAny(delim) >= 0)
                        parName = parName.Remove(parName.IndexOfAny(delim));
                    if (parName.IndexOfAny(delim) >= 0)
                        parName = parName.Remove(parName.IndexOfAny(delim));
                    AddOutParameter(parName);
                }
            }
#endif

            //create the DataAdapter & DataSet
            IDbDataAdapter da = CreateDataAdapter();
            da.SelectCommand = CommandObject;
            DataSet ds = new DataSet();

            //fill the DataSet using default values for DataTable names, etc.
            da.Fill(ds);
            //deal with {"The SqlParameter is already contained by another SqlParameterCollection."} exception.
            CommandObject.Parameters.Clear();
            return ds;
        }


        static void AssignParameters(IDbCommand cmd, params object[] parameterValues)
        {
            int CurrentField = 1;
            if ((parameterValues == null) || (parameterValues.Length <= 0)) return;
            //assign the provided values to these parameters based on parameter order
            for (int i = 0; i < parameterValues.Length; i++)
            {
                int posCurrentParameter = -1;
                if (cmd.CommandText.IndexOf("@") > 0)
                {
                    for (int j = 0; j <= i; j++)
                    {
                        posCurrentParameter = cmd.CommandText.IndexOf("@", posCurrentParameter + 1);
                    }
                    int iPosSpace = cmd.CommandText.IndexOf(" ", posCurrentParameter) - 1;
                    if (iPosSpace < 0)
                        iPosSpace = cmd.CommandText.Length - 1;
                    string sParamName = cmd.CommandText.Substring(posCurrentParameter + 1,
                                                                  iPosSpace - posCurrentParameter);
                    AddParameter(ParameterName("@" + sParamName), parameterValues[i]);
                }
                else
                {
                    AddParameter(ParameterName("@FLD" + CurrentField.ToString(CultureInfo.InvariantCulture)), parameterValues[i]);
                }
                CurrentField++;
                IDbDataParameter par = (IDbDataParameter)cmd.Parameters[i];
                par.Value = parameterValues[i];
                D2Bk.LogDebug("{0}: {1}", par.ParameterName, par.Value);
            }
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="spName">the name of the stored procedure</param>
        /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
        internal static int ExecuteStoredProcedure(string spName, params object[] parameterValues)
        {
            //if we receive parameter values, we need to figure out where they go
            if (CommandObject == null)
                return -1;
            CommandObject.CommandType = CommandType.StoredProcedure;
            CommandObject.CommandText = spName;
            D2Bk.LogDebug("EXEC: {0}", spName);
            AssignParameters(CommandObject, parameterValues);
            int result = CommandObject.ExecuteNonQuery();
            //deal with {"The SqlParameter is already contained by another SqlParameterCollection."} exception.
            CommandObject.Parameters.Clear();
            return result;
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="spName">the name of the stored procedure</param>
        /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
        internal static int ExecuteDynamicSql(string spName, params object[] parameterValues)
        {
            if (CommandObject == null)
                return -1;
            CommandObject.CommandType = CommandType.Text;
            CommandObject.CommandText = spName;
            D2Bk.LogDebug("EXEC: {0}", spName);
            AssignParameters(CommandObject, parameterValues);
            int result = CommandObject.ExecuteNonQuery();
            //deal with {"The SqlParameter is already contained by another SqlParameterCollection."} exception.
            CommandObject.Parameters.Clear();
            return result;
        }


        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="spName">the name of the stored procedure</param>
        /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
        internal static DataSet ExecuteDatasetForStoredProcedure(string spName, params object[] parameterValues)
        {
            //if we receive parameter values, we need to figure out where they go
            if (CommandObject == null)
                return default(DataSet);
            CommandObject.CommandType = CommandType.StoredProcedure;
            CommandObject.CommandText = spName;
            D2Bk.LogDebug("EXEC {0}", spName);
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                CommandObject.Parameters.Clear();
                //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                GetSpParameterSet(spName, false);

                //assign the provided values to these parameters based on parameter order
                for (int i = 0; i < CommandObject.Parameters.Count; i++)
                {
                    IDbDataParameter par = (IDbDataParameter)CommandObject.Parameters[i];
                    par.Value = parameterValues[i];
                    D2Bk.LogDebug("{0}: {1}", par.ParameterName, par.Value);
                }
            }

            //create the DataAdapter & DataSet
            IDbDataAdapter da = CreateDataAdapter();
            da.SelectCommand = CommandObject;
            DataSet ds = new DataSet();

            //fill the DataSet using default values for DataTable names, etc.
            da.Fill(ds);
            //Trying to deal with {"The SqlParameter is already contained by another SqlParameterCollection."} exception.
            CommandObject.Parameters.Clear();
            return ds;
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="spName">the name of the stored procedure</param>
        /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
        internal static object ExecuteScalar(string spName, params object[] parameterValues)
        {
            //if we receive parameter values, we need to figure out where they go
            if (CommandObject == null)
                return default(object);
            CommandObject.CommandType = CommandType.Text;
            CommandObject.CommandText = spName;
            D2Bk.LogDebug("EXEC: {0}", spName);
            AssignParameters(CommandObject, parameterValues);
            return ExecuteScalar();
        }


        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="sql">the name of the stored procedure</param>
        /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
        internal static DataSet ExecuteDatasetForSelectStatement(string sql, params object[] parameterValues)
        {
            //if we receive parameter values, we need to figure out where they go
            DataSet ds = new DataSet();
            if (CommandObject != null)
            {
                try
                {
                    new PermissionSet(PermissionState.Unrestricted).Assert();
                    CommandObject.CommandType = CommandType.Text;
                    //if the provider is not SqlClient, we have to use ? marks
                    if (ProviderType().IndexOf("SqlClient") < 0 && ProviderType().IndexOf("SQLite") < 0)
                    {
                        while (sql.IndexOf("@") > 0)
                        {
                            int posCurrentParameter = sql.IndexOf("@");
                            int iPosSpace = sql.IndexOf(" ", posCurrentParameter) - 1;
                            if (iPosSpace < 0)
                                iPosSpace = sql.Length - 1;
                            string sParamName = sql.Substring(posCurrentParameter + 1,
                                                              iPosSpace - posCurrentParameter);
                            sql = sql.Replace("@" + sParamName, " ? ");
                        }
                    }
                    CommandObject.CommandText = sql;
                    AssignParameters(CommandObject, parameterValues);
                    //create the DataAdapter & DataSet
                    IDbDataAdapter da = CreateDataAdapter();
                    da.SelectCommand = CommandObject;
                    D2Bk.LogDebug("EXEC: {0}", sql);

                    //fill the DataSet using default values for DataTable names, etc.
                    da.Fill(ds);
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message, ex);
                    ds.Dispose();
                    throw;
                }
                finally
                {
                    //deal with {"The SqlParameter is already contained by another SqlParameterCollection."} exception.
                    CommandObject.Parameters.Clear();
                    CodeAccessPermission.RevertAssert();
                }
            }
            return ds;
        }


        /// <summary>
        /// Executes D2Bk that is not a query.
        /// </summary>
        /// <returns>Rows affected.</returns>
        static internal IDataReader ExecuteReader()
        {
            return ExecuteReader(CommandBehavior.Default);
        }

        /// <summary>
        /// Executes D2Bk that is not a query.
        /// </summary>
        /// <returns>Rows affected.</returns>
        static internal int ExecuteNonQuery()
        {
            if (CommandObject == null)
                return -1;

            for (int i = 0; i < RetryTimes || RetryTimes == 0; i++)
            {
                try
                {
                    new PermissionSet(PermissionState.Unrestricted).Assert();
                    return CommandObject.ExecuteNonQuery();
                }
                catch (SqlException sqlex)
                {
                    HandleSqlException(sqlex);
                }
                catch (InvalidOperationException ioex)
                {
                    Thread.Sleep(i * 1000);
                    D2Bk.LogWarn("Exception: {0}; will recover if possible.", ioex.Message);
                }
                catch (Exception ex)
                {
                    D2Bk.LogFatal("Exception: {0}; should add recovery code if possible.", ex.Message);
                    throw;
                }
                finally
                {
                    //deal with {"The SqlParameter is already contained by another SqlParameterCollection."} exception.
                    CommandObject.Parameters.Clear();
                    CodeAccessPermission.RevertAssert();
                }
                RecoverConnection(i);
            }
            D2Bk.LogFatal("Could not recover");
            return -1;
        }
        /// <summary>
        /// Executes a scalar function.
        /// </summary>
        /// <returns>Return value. Must be converted to the proper type.</returns>
        static internal object ExecuteScalar()
        {
            if (CommandObject == null)
                return -1;

            for (int i = 0; i < RetryTimes || RetryTimes == 0; i++)
            {
                try
                {
                    new PermissionSet(PermissionState.Unrestricted).Assert();
                    return CommandObject.ExecuteScalar();
                }
                catch (SqlException sqlex)
                {
                    HandleSqlException(sqlex);
                }
                catch (InvalidOperationException ioex)
                {
                    Thread.Sleep(i * 1000);
                    D2Bk.LogWarn("Exception: {0}; will recover if possible.", ioex.Message);
                }
                catch (Exception ex)
                {
                    D2Bk.LogFatal("Exception: {0}; should add recovery code if possible.", ex.Message);
                    throw;
                }
                finally
                {
                    CommandObject.Parameters.Clear();
                    CodeAccessPermission.RevertAssert();
                }
                RecoverConnection(i);
            }
            D2Bk.LogFatal("Could not recover");
            return -1;
        }
        #region "Parameters"
        /// <summary>
        /// Clear all parameters for command on current thread.
        /// </summary>
        static internal void ClearParameters()
        {
            if (CommandObject == null)
                return;
            CommandObject.CommandType = CommandType.Text;
            D2Bk.d2BkSqlParameters.CurrentField = 1;
            CommandObject.Parameters.Clear();
        }

        /// <summary>
        /// Add new parameter to connection,
        /// </summary>
        /// <param name="paramName">Parameter name.</param>
        /// <param name="dbType">D2Bk type.</param>
        static internal void AddParameter(string paramName, DbType dbType)
        {
            if (CommandObject == null)
                return;
            if (!paramName.StartsWith("@"))
            {
                paramName = "@" + paramName;
            }
            IDbDataParameter par = CommandObject.CreateParameter();
            par.DbType = dbType;
            par.ParameterName = paramName;
            CommandObject.Parameters.Add(par);
        }

        /// <summary>
        /// Add new parameter to connection.
        /// </summary>
        /// <param name="paramName">Parameter name.</param>
        /// <param name="value">Parameter value.</param>
        static internal void AddOraParameter(string paramName, object value)
        {
            if (CommandObject == null)
                return;
            paramName = paramName.Trim();
            if (ProviderType().IndexOf("Oracle") >= 0)
            {
                if (!paramName.StartsWith(":"))
                {
                    paramName = ":" + paramName;
                    if (value == null)
                        value = DBNull.Value;
                }
                paramName = paramName.Replace(":", "");
            }
            else
            {
                if (!paramName.StartsWith("@"))
                {
                    paramName = "@" + paramName;
                }
            }
            IDbDataParameter par = CommandObject.CreateParameter();
            par.ParameterName = paramName;
            par.Value = value;
            //    par.DbType = DbType.String;
            //    par.Size = 50;
            CommandObject.Parameters.Add(par);
        }
        /// <summary>
        /// Add new parameter to connection.
        /// </summary>
        /// <param name="paramName">Parameter name.</param>
        /// <param name="value">Parameter value.</param>
        static internal void AddParameter(string paramName, object value)
        {
            if (CommandObject == null)
                return;
            paramName = paramName.Trim();
            if (ProviderType().IndexOf("Oracle") >= 0)
            {
                if (!paramName.StartsWith(":"))
                {
                    paramName = ":" + paramName;
                    if (value == null)
                        value = DBNull.Value;
                    if (value is string)
                        if ((string)value == "")
                            value = DBNull.Value;
                    if (value is Boolean)
                        value = (Boolean)value ? 1 : 0;
                }
                paramName = paramName.Replace(":", "");
            }
            else
            {
                if (!paramName.StartsWith("@"))
                {
                    paramName = "@" + paramName;
                }
            }
            IDbDataParameter par = CommandObject.CreateParameter();
            if (value.GetType() == typeof(string))
            {
                par.DbType = DbType.AnsiString;
            }
            par.ParameterName = paramName;
            par.Value = value;
            D2Bk.LogDebug("{0}: {1}", paramName, value);
            CommandObject.Parameters.Add(par);
        }

#if Oracle
    /// <summary>
    /// Add new parameter to connection.
    /// </summary>
    /// <param name="paramName">Parameter name.</param>
        static internal void AddOutParameter(string paramName)
        {
            //IDbDataParameter par = GetCurrentCommandObject().CreateParameter();
            //par.ParameterName = paramName;
            //par.Value = DBNull.Value;
            ((OracleCommand) GetCurrentCommandObject()).Parameters.Add(paramName, OracleDbType.RefCursor,
                                   DBNull.Value, ParameterDirection.Output);

            //par.DbType = OracleDbType.RefCursor;
            //par.DbType = (DbType) Oracle.DataAccess.Client.OracleDbType.RefCursor;
            //par.DbType = DbType.Object;
            //par.Direction = ParameterDirection.Output;
            //GetCurrentCommandObject().Parameters.Add(par);
        }
#endif
        /// <summary>
        /// Set parameter value.
        /// </summary>
        /// <param name="paramName">Parameter name.</param>
        /// <param name="value">Parameter value.</param>
        static internal void SetParameter(string paramName, object value)
        {
            if (CommandObject == null)
                return;

            paramName = paramName.Trim();
            if (!paramName.Trim().StartsWith("@"))
            {
                paramName = "@" + paramName;
            }
            IDbDataParameter par = (IDbDataParameter)CommandObject.Parameters[paramName];
            if (value is DateTime)
            {
                if (((DateTime)value) == DateTime.MinValue)
                {
                    par.Value = null;
                    return;
                }
            }
            if (value == null)
            {
                par.Value = null;
            }
        }

        //deep copy of cached SqlParameter array
        private static IDbDataParameter[] CloneParameters(IDbDataParameter[] originalParameters)
        {
            IDbDataParameter[] clonedParameters = new IDbDataParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (IDbDataParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }
        [ThreadStatic]
        private static Dictionary<string, IDbDataParameter[]> cachedParameters;

        internal static IDbDataParameter[] GetSpParameterSet(string spName, bool includeReturnValueParameter)
        {
            if (CommandObject == null)
                return null;
            if (string.IsNullOrEmpty(spName))
                return null;
            if (cachedParameters == null)
                cachedParameters = new Dictionary<string, IDbDataParameter[]>();
            if (!cachedParameters.ContainsKey(spName))
            {
                cachedParameters.Add(spName, DiscoverSpParameterSet(spName, includeReturnValueParameter));
            }
            else
            {
                CommandObject.Parameters.Clear();
                CommandObject.Parameters.CopyTo(CloneParameters(cachedParameters[spName]), 0);
                foreach (IDbDataParameter param in cachedParameters[spName])
                    CommandObject.Parameters.Add(param);
            }
            return CloneParameters(cachedParameters[spName]);
        }


        internal static IDbDataParameter[] DiscoverSpParameterSet(string spName, bool includeReturnValueParameter)
        {
            if (CommandObject == null)
                return null;

            CommandObject.CommandType = CommandType.StoredProcedure;

            if (CommandObject.GetType() == typeof(SqlCommand))
                SqlCommandBuilder.DeriveParameters((SqlCommand)CommandObject);
            else if (CommandObject.GetType() == typeof(OdbcCommand))
                OdbcCommandBuilder.DeriveParameters((OdbcCommand)CommandObject);
            else if (CommandObject.GetType() == typeof(OleDbCommand))
                OleDbCommandBuilder.DeriveParameters((OleDbCommand)CommandObject);
            else if (CommandObject.GetType() == typeof(Oracle.DataAccess.Client.OracleCommand))
            {
                Oracle.DataAccess.Client.OracleCommandBuilder.DeriveParameters(
                    (Oracle.DataAccess.Client.OracleCommand) CommandObject);
                //TODO: Investigate why this was necessary.
                includeReturnValueParameter = true;
            }
            if (!includeReturnValueParameter)
            {
                CommandObject.Parameters.RemoveAt(0);
            }

            IDbDataParameter[] discoveredParameters = new IDbDataParameter[CommandObject.Parameters.Count];
            CommandObject.Parameters.CopyTo(discoveredParameters, 0);
            return discoveredParameters;
        }

        private static IDbDataAdapter CreateDataAdapter()
        {
            if (CommandObject == null)
                return null;

            if (CommandObject.GetType() == typeof(SqlCommand))
                return new SqlDataAdapter();
            if (CommandObject.GetType() == typeof(OdbcCommand))
                return new OdbcDataAdapter();
            if (CommandObject.GetType() == typeof(OleDbCommand))
                return new OleDbDataAdapter();
            D2BkSettingsCollection settings = new D2BkSettingsCollection();
            string dataAdapter = settings["dataAdapterType"];
            if (!string.IsNullOrEmpty(dataAdapter))
            {
                Assembly referencedAssembly = Assembly.Load(GetAssemblyName()) ?? Assembly.Load(GetAssemblyName());
                Type adapterType = referencedAssembly.GetType(dataAdapter);
                return (IDbDataAdapter)Activator.CreateInstance(adapterType);
            }
            throw new D2BkException("Invalid data adapter");
        }



        #endregion
        /// <summary>
        /// Gets command text for command on current thread.
        /// </summary>
        static internal string GetCommandText()
        {
            return CommandObject == null ? null : CommandObject.CommandText;
        }
        static internal void SetCommandText(string value)
        {
            Log.DebugFormat("D2Bk: {0}", value);
            if (CommandObject == null)
                return;
            CommandObject.CommandText = value;
        }
        private bool disposed;

        public void Dispose()
        {
            Dispose(true);
        }

        private void Dispose(bool disposing)
        {
            if (!disposed)
            {
                // Set the sentinel.
                disposed = true;

                // Suppress finalization of this disposed instance.
                if (disposing)
                {
                    GC.SuppressFinalize(this);
                }
            }
        }


        /// <summary>
        /// Finalizer implementation.
        /// </summary>
        ~D2BkConnection()
        {
            Dispose(false);
        }




    }
}